﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;


/*
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'  --禁用数据库中所有表的所有触发器

EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' --启用数据库中所有表的所有触发器

--禁用 
alter table [tablename] 
disable all trigger 

--恢复 
alter table [tablename] 
enable all trigger 
 */
namespace OISTools
{
    public partial class ImportDataFrom02To08 : Form
    {
        public ImportDataFrom02To08()
        {
            InitializeComponent();
        }
        DataAccess.SqlProvider db1, db2;
        string sourceDB = "Con2002_NJHJ", targetDB = "Con2008_NJHJ_K";
        string[] listTableNames = new string[] { "bajar", "bajarb", "car", "caremp", "caroil", "carwx", "cust", "dept", "employee", "formulab", "formulablog", "formulah", "formulahlog", "formulasb", "formulash", "formulatb", "formulath", "gpscar", "INCCreditmn", "IncCustCreditb", "IncCustCredith", "incorech", "incpayh", "ins_other", "material", "mincard", "mindailyb", "mindailys", "minimportb", "minimporth", "minmater", "minrpt", "minrsab", "minrsah", "minrsb", "minrsh", "minstock", "minstockmn", "ogen", "ordb", "ordc", "ordh", "ordtpy", "qccadjust", "qccbsstdb", "qccsa", "qccssstdb", "region", "shipb", "shipbajar", "shipCredit", "shiph", "shiphordc", "shiprpt", "stdpdef", "vend", "vendMatUniprice", "vendTranUniprice", "XXuniprice", "_aux" };
        private void ImportDataFrom02To08_Load(object sender, EventArgs e)
        {
            db1 = new DataAccess.SqlProvider("server=192.168.1.100;uid=sa;pwd=`12345;database=Con2002_NJHJ;");
            db2 = new DataAccess.SqlProvider("server=192.168.1.100;uid=sa;pwd=`12345;database=Con2008_NJHJ_K;");


        }

        private void btnStart_Click(object sender, EventArgs e)
        {

            StringBuilder sbTextColl=new StringBuilder();
            foreach (string tableName in listTableNames)
            {
                DataTable dtSource = getSourceTable(tableName);
                DataTable dtTarget = getTargetTable(tableName);

                if (dtSource.Rows.Count <= 0)
                    continue;

                string sqlText = "";
                string fieldsText = "";
                foreach (DataRow drTarget in dtTarget.Rows)
                {
                    string s;
                    string name=drTarget["name"].ToString();
                    DataRow drSource = getRelateRow(dtSource,name);

                    if (drSource == null)
                    {
                        if (drTarget["isnullable"].ToString() == "1")
                            fieldsText += string.Format(",null as [{0}]", name);
                        else
                        {
                            if (drTarget["type"] == "39")
                                fieldsText += string.Format(",'' as [{0}]", name);
                            else
                            {
                                fieldsText += string.Format(",0 as [{0}]", name);
                            }
                        }
                    }
                    else if (drSource["usertype"].ToString() == "258")
                    {
                        fieldsText += string.Format(",dbo.fn_getsplitDate([{0}]) as [{0}]", name);
                    }
                    else if (drSource["usertype"].ToString() != "0")
                    {
                        fieldsText += string.Format(",[{0}]", name);
                    }

                }
                if (fieldsText.Length > 0)
                    fieldsText = fieldsText.Substring(1);



                sqlText = string.Format(@"
delete [{0}]..[{1}];
insert into [{0}]..[{1}] select {2}  FROM [{3}]..[{1}];
GO

", targetDB, tableName, fieldsText, sourceDB);

                sbTextColl.Append(sqlText);
            }
            richTextBox1.Text = sbTextColl.ToString();

        }

        private DataRow getRelateRow(DataTable dtSource,string name)
        {
            foreach (DataRow dr in dtSource.Rows)
            {
                if (dr["name"].ToString() == name)
                    return dr;
            }
            return null;
        }


        private DataTable getTargetTable(string tableName)
        {
            string sqlText = string.Format(@"select col.name,usertype,isnullable,col.type from sys.syscolumns as col inner join sys.sysobjects as obj  on obj.id=col.id and  obj.name='{0}' order by colid", tableName);
            return db2.GetDataTable(sqlText);
        }

        private DataTable getSourceTable(string tableName)
        {
            string sqlText = string.Format(@"select col.name,usertype,isnullable,col.type from sys.syscolumns as col inner join sys.sysobjects as obj  on obj.id=col.id and  obj.name='{0}' order by colid", tableName);
            return db1.GetDataTable(sqlText);
        }

    }
}
